package net.sapfree.quan.sql;

import net.sapfree.core.util.UUIDGenerator;

public class MessageBoradSql {
	public static String getMessageByQuanId(String quanId){
		StringBuffer sql=new StringBuffer();
		sql.append("SELECT c.* FROM CommentContent c,CommentRelation r ")
		   .append("WHERE r.cr_uuid=c.cc_uuid AND r.cr_quanuuid='").append(quanId).append("' order by CC_COMMENTTIME desc limit 0,5");
		return sql.toString();
	}
	
	public static String getMessageByPersonId(String personId){
		StringBuffer sql=new StringBuffer();
		sql.append("SELECT c.*,p.pi_yszhaopian FROM CommentContent c,CommentRelation r,personinfo p ")
		   .append("WHERE r.cr_uuid=c.cc_uuid AND r.cr_personuuid='").append(personId).append("' and c.cc_personuuid=p.pi_uuid order by CC_COMMENTTIME desc  ");
		return sql.toString();
	}
	
	
	public static String getMessageByMsgId(String msgId){
		StringBuffer sql=new StringBuffer();
		sql.append("SELECT c.*,p.pi_yszhaopian FROM CommentContent c,personinfo p ")
		   .append("WHERE   c.cc_uuid='").append(msgId).append("' and c.cc_personuuid=p.pi_uuid   ");
		return sql.toString();
	}
	
	public static String relist(String msgId){
		StringBuffer sql=new StringBuffer();
		sql.append("SELECT c.*,r.cr_ccid,p.pi_yszhaopian FROM CommentContent c,CommentRelation r,personinfo p  ")
		   .append("WHERE r.cr_uuid=c.cc_uuid AND r.cr_ccid='").append(msgId).append("' and c.cc_personuuid=p.pi_uuid  order by CC_COMMENTTIME desc ");
		return sql.toString();
	}
	
	public static String relistCount(String msgId){
		StringBuffer sql=new StringBuffer();
		sql.append("SELECT count(*) AS COUNTNUM FROM CommentContent c,CommentRelation r  ")
		   .append("WHERE r.cr_uuid=c.cc_uuid AND r.cr_ccid='").append(msgId).append("'    ");
		return sql.toString();
	}
	
	
	
	public static String getFriendListSql(String personuuid){
		StringBuffer sql = new StringBuffer();
	 
		 sql.append(" SELECT p.* FROM PERSONINFO p where p.pi_uuid IN( ")
		    .append(" select fr_personuuid FROM  FRIENDRELATION f where fr_personuuid ='")
		    .append(personuuid).append("' OR  fr_frienduuid ='").append(personuuid).append("')");
		
		return sql.toString();
	}
	
	
	public static String ajaxGetDongtaiByQuanId(String quanId){
		StringBuffer sql=new StringBuffer();
		sql.append("SELECT * FROM quanactive r ")
		   .append("WHERE  r.qa_quanuuid='").append(quanId).append("' order by qa_avtivetime desc limit 0,5");
		return sql.toString();
	}
	
	public static String getVoteOptionByVoteId(String voteId){
		StringBuffer sql=new StringBuffer();
		sql.append("SELECT v.* FROM VoteOption v  ")
		   .append(" where v.vo_voteuuid='").append(voteId).append("' order by vo_voteindex  ");
		return sql.toString();
	}
	
	
	public static String updateVoteCount(String[] voteResult){
		StringBuffer sql=new StringBuffer();
		sql.append(" update VoteOption set vo_votecount=vo_votecount+1 ")
		   .append(" where  vo_uuid IN (");
		for(int i=0;i<voteResult.length;i++){
			sql.append("'").append(voteResult[i]).append("',");
		}
		sql.deleteCharAt(sql.length()-1);
		sql.append(")");
		return sql.toString();
	}
	
	
	
	
	public static String getEventByArticleId(String articleId){
		StringBuffer sql=new StringBuffer();
		sql.append("SELECT e.*")
		    .append(" FROM EventLog e  ")
		    .append(" where e.el_articleuuid='").append(articleId).append("' order by el_date  ");
		return sql.toString();
	}
	
	
	
	public static String addOption(String voteId,String optionName){
		StringBuffer sql=new StringBuffer();
		String pk=UUIDGenerator.getUUID();
		sql.append(" insert into voteoption(vo_uuid,vo_voteuuid,vo_votetitle,vo_voteindex,vo_votecount )");
		sql.append(" select '").append(pk).append("','").append(voteId).append("','").append(optionName)
		   .append("',MAX(vo_voteindex)+1,0 FROM voteoption v")
		   .append(" where  vo_voteuuid='").append(voteId).append("'");
		
		return sql.toString();
	}
	
	
	
	public static String addMessageCount(String quanId){
		StringBuffer sql=new StringBuffer();
		 
		sql.append("  UPDATE  quaninfo set quan_commentcount=quan_commentcount+1 where quan_uuid='").append(quanId).append("'");
		 
		
		return sql.toString();
	}
	
}
